package com.authine.cloudpivot.ext.utils;

import com.authine.cloudpivot.ext.dto.CustomerStatistics.CustomerStatisticInfoDto;
import com.authine.cloudpivot.ext.dto.CustomerStatistics.CustomerStatisticResultDto;
import com.authine.cloudpivot.ext.dto.CustomerStatistics.CustomerStatisticsDto;
import com.authine.cloudpivot.ext.dto.OrgInfoDto;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import com.authine.cloudpivot.ext.dto.system.UserDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class CustomerStatisticsJdbcUtil {
    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);

    public static int queryAllCustomerCount(CustomerStatisticsDto customerStatisticsDto) {
        NamedParameterJdbcTemplate nameParameterJdbcTemplate = ApplicationContextUtils.getBean(NamedParameterJdbcTemplate.class);
        Map<String,Object> param = new HashMap<>();
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) from systemmanagement.i_systemmanagement_t_organization where role_names like '%ROLE_CUSTOMER_ADMIN%' and deleted =0 ");
        if (StringUtils.isNotBlank(customerStatisticsDto.getOrgName())){
            sb.append(" and name like :name ");
            param.put("name","%" + customerStatisticsDto.getOrgName() + "%");
        }
        return nameParameterJdbcTemplate.queryForObject(sb.toString(), param, Integer.class);
    }


    public static List<Map<String,Object>> queryAllCustomerInfos(CustomerStatisticsDto customerStatisticsDto,Integer start,Integer size) {
        Map<String,Object> param = new HashMap<>();
        StringBuffer sb = new StringBuffer();
        sb.append("select id,name from systemmanagement.i_systemmanagement_t_organization where role_names like '%ROLE_CUSTOMER_ADMIN%' and deleted =0 ");
        if (StringUtils.isNotBlank(customerStatisticsDto.getOrgName())){
            sb.append (" and name like  '%"+ customerStatisticsDto.getOrgName()+"%'");
        }
        if (start != null  && size != null){
            sb.append( " limit "+start +"," + size);
        }
        return jdbcTemplate.queryForList(sb.toString());
    }
    public static List<CustomerStatisticInfoDto> queryAllCustomers(CustomerStatisticsDto customerStatisticsDto,Integer start,Integer size) {
        Map<String,Object> param = new HashMap<>();
        StringBuffer sb = new StringBuffer();
        sb.append("select id,name from systemmanagement.i_systemmanagement_t_organization where role_names like '%ROLE_CUSTOMER_ADMIN%' and deleted =0 ");
        if (StringUtils.isNotBlank(customerStatisticsDto.getOrgName())){
            sb.append (" and name like  '%"+ customerStatisticsDto.getOrgName()+"%'");
        }
        sb.append( " limit "+start +"," + size);
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticInfoDto>(CustomerStatisticInfoDto.class));
    }

    public static List<CustomerStatisticResultDto> queryInquiryGoodNum(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) total,organization_company_name name from sourcingcenter.i_sourcingcenter_t_inquiry_goods itig where 1=1 ");
        sb.append(" and deleted = 0");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by organization_company_name");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));


    }

    public static List<CustomerStatisticResultDto> quiryInqueryGoodClarifyCount(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) total,organization_company_name name from sourcingcenter.i_sourcingcenter_t_inquiry_goods itig where 1=1 ");
        sb.append(" and deleted = 0 and clarify_record =1 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by organization_company_name");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));


    }

    public static List<CustomerStatisticResultDto> quiryQuotationQuoteCount(CustomerStatisticsDto good, StringBuffer sbNames) {

        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) total,organization_company_name name from sourcingcenter.i_sourcingcenter_t_inquiry_goods itig where 1=1 ");
        sb.append(" and deleted = 0 and quote_count =0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }

        sb.append(" group by organization_company_name");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));

    }

    public static List<CustomerStatisticResultDto> queryOldVendorInquiryGoodCount(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) total,organization_company_name name from sourcingcenter.i_sourcingcenter_t_inquiry_goods itig where 1=1 ");
        sb.append(" and deleted = 0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }

        sb.append(" group by organization_company_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));

    }

    public static List<CustomerStatisticResultDto> queryCustomerOrderGoodNum(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) total,sum(total) orderTotal,customer_organization_name name from ordercenter.i_ordercenter_t_order_customer_goods itig where 1=1 ");
        sb.append(" and deleted = 0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and customer_organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }

        sb.append(" group by customer_organization_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));
    }

    public static List<CustomerStatisticResultDto> queryOldVendorOrderGoodNum(CustomerStatisticsDto good, StringBuffer sbNames,Integer type) {
        StringBuffer sb = new StringBuffer();
        sb.append("select");
        sb.append(" count(1) total,");
        sb.append(" vendorOrderGoods.customer_organization_name name ");
        sb.append(" from ordercenter.i_ordercenter_t_order_vendor_goods vendorOrderGoods");
        sb.append(" left join  ordercenter.i_ordercenter_t_order_vendor vendorOrder  on vendorOrderGoods.order_vendor_id = vendorOrder.id and vendorOrder.deleted=0");
        sb.append(" left join systemmanagement.i_systemmanagement_t_organization  org  on org.platform_id =vendorOrder.organization_id and org.deleted=0 ");
        sb.append(" where 1=1");
        sb.append(" and vendorOrderGoods.deleted = 0 and vendorOrderGoods.customer_organization_name is not null ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and vendorOrderGoods.customer_organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and vendorOrderGoods.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and vendorOrderGoods.createdTime <= '" + good.getEndTime() + "' ");
        }
        if (type != null){
            sb.append(" and org.platform = " +type);
        }

        sb.append(" group by vendorOrderGoods.customer_organization_name");

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));


    }


    public static List<CustomerStatisticResultDto> queryCustomerOrderTotal(CustomerStatisticsDto good, StringBuffer sbNames) {

        StringBuffer sb = new StringBuffer();
        sb.append("select");
        sb.append(" count(1) total,");
        sb.append(" vendorOrder.customer_organization_name name from ordercenter.i_ordercenter_t_order_vendor vendorOrder");
        sb.append(" left join systemmanagement.i_systemmanagement_t_organization  org  on org.platform_id =vendorOrder.organization_id and org.deleted=0 ");
        sb.append(" and vendorOrder.deleted = 0 and vendorOrder.customer_organization_name is not null ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and vendorOrder.customer_organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and vendorOrder.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and vendorOrder.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by vendorOrder.customer_organization_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));

    }

    public static List<CustomerStatisticResultDto> queryVendorOrderGoodTotal(CustomerStatisticsDto good, StringBuffer sbNames, Integer type) {
        StringBuffer sb = new StringBuffer();
        sb.append("select sum(total) total,customer_organization_name name from ordercenter.i_ordercenter_t_order_vendor_goods   vendorOrder  ");
        sb.append(" left join systemmanagement.i_systemmanagement_t_organization  org  on org.platform_id =vendorOrder.vendor_organization_id and org.deleted=0 ");
        sb.append( " where 1=1 ");
        sb.append(" and vendorOrder.deleted = 0 and customer_organization_name is not null ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and vendorOrder.customer_organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and vendorOrder.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and vendorOrder.createdTime <= '" + good.getEndTime() + "' ");
        }
        if (type != null){
            sb.append(" and org.platform = " +type);
        }
        sb.append(" group by vendorOrder.customer_organization_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));


    }

    public static List<CustomerStatisticResultDto> queryInquiryVendorScope(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select count(good.id) total,good.organization_company_name name from sourcingcenter.i_sourcingcenter_t_inquiry_goods good");
        sb.append(" left join sourcingcenter.i_sourcingcenter_t_inquiry inquiry on inquiry.id= good.inquiry_id and inquiry.deleted=0");
        sb.append( " where good.deleted=0 and  inquiry.vendor_scope in (1,3) and good.organization_company_name != null");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and good.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and good.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and good.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by good.organization_company_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));
    }
    public static List<CustomerStatisticResultDto> queryInquiryGoodPicketagedNum(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select count(good.id) total,good.organization_company_name name from sourcingcenter.i_sourcingcenter_t_inquiry_goods good");
        sb.append(" left join sourcingcenter.i_sourcingcenter_t_inquiry inquiry on inquiry.id= good.inquiry_id and inquiry.deleted=0");
        sb.append( " where good.deleted=0 and good.organization_company_name != null");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and good.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and good.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and good.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" and good.picketaged=1");
        sb.append(" group by good.organization_company_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));
    }
    public static List<CustomerStatisticResultDto> querySumQuotationNum(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT");
        sb.append(" sum( quote_count ) total,");
        sb.append(" organization_company_name name");
        sb.append(" FROM");
        sb.append(" sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append( " where deleted=0 and organization_company_name != null");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by organization_company_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<CustomerStatisticResultDto>(CustomerStatisticResultDto.class));
    }
    public static List<CustomerStatisticResultDto> queryAverageQuotationCount(CustomerStatisticsDto good, StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT");
        sb.append(" sum( quote_count ) total,");
        sb.append(" organization_company_name name");
        sb.append(" FROM");
        sb.append(" sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append( " where deleted=0 and organization_company_name != null");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by organization_company_name");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }


    public static List<CustomerStatisticResultDto> queryVendorQuotationNum(StringBuffer sbNames, CustomerStatisticsDto good,boolean isGroupByTime) {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT inquiry.organization_company_name name,count(quotation.id) count");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d') time");
        }
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_quotation_goods quotation");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods goods ON goods.id = quotation.inquiry_goods_id and goods.deleted=0 ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry ON inquiry.id = goods.inquiry_id and inquiry.deleted=0");
        sb.append(" WHERE quotation.price IS NOT NULL  and quotation.deleted=0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and quotation.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and quotation.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and quotation.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by inquiry.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d')");
        }

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));


    }

    public static List<CustomerStatisticResultDto> queryPlateQuotationNum(StringBuffer sbNames, CustomerStatisticsDto good, boolean isGroupByTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT inquiry.organization_company_name name");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d') time");
        }
        sb.append(" ,count(quotation.id) count ");
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_quotation_goods quotation");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods goods ON goods.id = quotation.inquiry_goods_id and goods.deleted=0");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry ON inquiry.id = goods.inquiry_id and inquiry.deleted=0");
        sb.append(" WHERE");
        sb.append( " quotation.vendor_organization_id NOT IN (");
        sb.append(" SELECT DISTINCT vendor_organization_id  FROM personalcenter.i_personalcenter_t_organization_invite_code CODE ");
        sb.append(" WHERE CODE.deleted = 0 ");
        sb.append(" AND CODE.vendor_organization_id IS NOT NULL )");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and inquiry.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and quotation.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and quotation.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" and quotation.deleted=0 ");
        sb.append(" GROUP BY inquiry.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d')");
        }
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }

    public static List<CustomerStatisticResultDto> queryVendorDingbiaoNum(StringBuffer sbNames, CustomerStatisticsDto good, boolean isGroupByTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("select inquiry.organization_id organizationId,");
        if (isGroupByTime){
            sb.append(" date_format(quotation.createdTime,'%Y-%m-%d') time,");
        }
        sb.append(" count( quotation.id ) count ");
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_quotation_goods quotation");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods goods ON goods.id = quotation.inquiry_goods_id");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry ON inquiry.id = goods.inquiry_id ");
        sb.append(" WHERE");
        sb.append( " quotation.picketage = TRUE ");

        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and quotation.vendor_organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and quotation.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and quotation.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" and quotation.deleted=0 ");
        sb.append(" GROUP BY inquiry.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d')");
        }
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));



    }

    public static List<CustomerStatisticResultDto> queryPlateVendorDingbiaoNum(StringBuffer sbNames, CustomerStatisticsDto good, boolean isGroupByTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  inquiry.organization_company_name name,");
        if (isGroupByTime){
            sb.append(" date_format(quotation.createdTime,'%Y-%m-%d') time,");
        }
        sb.append(" count(quotation.id) count ");
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_quotation_goods quotation");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods goods ON goods.id = quotation.inquiry_goods_id and goods.deleted=0");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry ON inquiry.id = goods.inquiry_id and inquiry.deleted=0");
        sb.append(" WHERE");
        sb.append( " quotation.vendor_organization_id NOT IN (");
        sb.append(" SELECT DISTINCT vendor_organization_id  FROM personalcenter.i_personalcenter_t_organization_invite_code CODE ");
        sb.append(" WHERE CODE.deleted = 0  ");
        sb.append(" AND CODE.vendor_organization_id IS NOT NULL )");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and inquiry.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and quotation.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and quotation.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" and quotation.deleted=0  and quotation.picketage = true");
        sb.append(" GROUP BY inquiry.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d')");
        }
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));

    }

    public static List<CustomerStatisticResultDto> queryCustomerInquiryGoodNum(StringBuffer sbNames, CustomerStatisticsDto good, boolean isGroupByTime) {

        StringBuffer sb = new StringBuffer();
        sb.append("select inquiry.organization_company_name name,");
        if (isGroupByTime){
            sb.append(" date_format(goods.createdTime,'%Y-%m-%d') time,");
        }
        sb.append(" count(goods.id) count ");
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_inquiry_goods goods ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry ON inquiry.id = goods.inquiry_id ");
        sb.append(" WHERE goods.deleted= 0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and inquiry.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and goods.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and goods.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" GROUP BY inquiry.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(goods.createdTime,'%Y-%m-%d')");
        }
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }

    public static List<CustomerStatisticResultDto> queryUnQuotationGoodNum(StringBuffer sbNames, CustomerStatisticsDto good, boolean isGroupByTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT inquiry.organization_company_name name,count( goods.id ) count");
        if (isGroupByTime){
            sb.append(",date_format(goods.createdTime,'%Y-%m-%d') time ");
        }
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_quotation_goods quotation");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods goods ON goods.id = quotation.inquiry_goods_id and goods.deleted=0 ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry ON inquiry.id = goods.inquiry_id and inquiry.deleted=0");
        sb.append(" WHERE quotation.price IS  NULL  and quotation.deleted=0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and inquiry.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and quotation.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and quotation.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by inquiry.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(quotation.createdTime,'%Y-%m-%d')");
        }

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }

    public static List<Map<String, Object>> queryVendorOrderGoodNum(StringBuffer sbNames, CustomerStatisticsDto good, String type, boolean isGroupByTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "  co.organization_name name,");
        if (isGroupByTime){
            sb.append( "date_format(order_goods.createdTime,'%Y-%m-%d')time ,");
        }
        sb.append("  count( order_goods.quantity ) quantity,\n" +
                "  sum( order_goods.amount ) total, \n" +
                "  sum( refund_trade.refundAmount ) refundAmount  \n" +
                "FROM\n" +
                "  ordercenter.i_ordercenter_t_order_vendor_goods order_goods\n" +
                "  LEFT JOIN ordercenter.i_ordercenter_t_order_vendor vo ON vo.ID = order_goods.order_vendor_id\n" +
                "  LEFT JOIN ordercenter.i_ordercenter_t_order_customer co ON co.ID = vo.order_customer_id\n" +
                "  LEFT JOIN (\n" +
                "  SELECT\n" +
                "    r.order_vendor_goods_id,\n" +
                "    sum( r.quantity )* o.price refundAmount \n" +
                "  FROM\n" +
                "    ordercenter.i_ordercenter_t_order_goods_returns r\n" +
                "    LEFT JOIN ordercenter.i_ordercenter_t_order_vendor_goods o ON r.order_vendor_goods_id = o.id \n" +
                "  WHERE\n" +
                "    r.deleted = 0 \n" +
                "    AND o.deleted = 0 \n" +
                "  GROUP BY\n" +
                "    r.order_vendor_goods_id,\n" +
                "    o.price \n" +
                "  ) refund_trade ON refund_trade.order_vendor_goods_id = order_goods.id ");
        sb.append(" where ");
        if(StringUtils.isNotBlank(type)){
            sb.append(type );
        }
        sb.append(" EXISTS (\n" +
                "  SELECT CODE\n" +
                "    .vendor_organization_id \n" +
                "  FROM\n" +
                "    personalcenter.i_personalcenter_t_organization_invite_code CODE \n" +
                "  WHERE\n" +
                "    CODE.deleted = 0 \n" +
                "    AND CODE.vendor_organization_id is not null \n" +
                "    AND CODE.customer_organization_id = co.organization_id \n" +
                "    AND CODE.vendor_organization_name = vo.organization_name \n" +
                "  ) \n" +
                "  and co.after_sale ='已售后' and co.order_customer_status in ('UpcomingConfirm','Done')\n" +
                "  AND (\n" +
                "    (order_goods.label != 6  and (order_goods.order_vendor_goods_status != 'Closed' )  \n" +
                "    OR ( order_goods.label = 6  and (order_goods.order_vendor_goods_status = 'Closed') AND refund_trade.refundAmount IS NOT NULL )) \n" +
                "  ) \n" +
                "  AND order_goods.deleted = 0 ");
        //TODO
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and co.organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and order_goods.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and order_goods.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by co.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(order_goods.createdTime,'%Y-%m-%d')");
        }
        return jdbcTemplate.queryForList(sb.toString());

    }


    public static double sub(double v1,double v2){
        BigDecimal b1 = new BigDecimal(Double.toString(v1));
        BigDecimal b2 = new BigDecimal(Double.toString(v2));
        return b1.subtract(b2).doubleValue();
    }


    public static List<CustomerStatisticResultDto> queryOrderNum(StringBuffer sbNames, CustomerStatisticsDto good, boolean isGroupByTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT customer_order.organization_name name,");
        if (isGroupByTime){
            sb.append("date_format(customer_order.createdTime,'%Y-%m-%d') time,");
        }
        sb.append(" count( customer_order.id ) count");
        sb.append(" from ordercenter.i_ordercenter_t_order_customer customer_order ");
        sb.append(" WHERE customer_order.deleted = 0 ");
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and customer_order.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and customer_order.createdTime <= '" + good.getEndTime() + "' ");
        }
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and customer_order.organization_name in ('" + sbNames + "')");
        }
        sb.append(" GROUP BY customer_order.organization_id");
        if (isGroupByTime){
            sb.append(",date_format(customer_order.createdTime,'%Y-%m-%d')");
        }
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }



    public static List<Map<String,Object>> queryProjectManager(String name,StringBuffer sbNames) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT");
        sb.append(" tp.organization_name name,");
        sb.append(" GROUP_CONCAT(DISTINCT tp.member_name) AS fullname,");
        sb.append(" from operation1center.i_operation1center_t_platform_grant_user tp ");
        sb.append(" WHERE tp.deleted = 0");
        if (StringUtils.isNotBlank(name)){
            sb.append(" and tp.member_name in ('" + name + "')");
        }
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" tp.organization_name in ('" + sbNames + "')");
        }
        sb.append(" GROUP BY tp.organization_id");
        return jdbcTemplate.queryForList(sb.toString());

    }

    public static List<UserDto> queryCustomerUserDepartment(String names) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT department_name,GROUP_CONCAT(username) userName FROM systemmanagement.i_systemmanagement_t_user  WHERE department_name in ('"+names+"') ");
        sb.append(" and deleted= 0 GROUP BY department_name ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(UserDto.class));
    }
    public static List<Map<String,Object>> queryInquiryGoodExport(CustomerStatisticsDto good){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT");
        sb.append(" i.createdTime AS time,");
        sb.append(" i.expire AS expire,");
        sb.append(" g.inquiry_id AS inquiryId,");
        sb.append(" g.id AS id,");
        sb.append(" g.name AS name,");
        sb.append(" g.brand AS brand,");
        sb.append(" g.spec AS spec,");
        sb.append(" g.model AS model,");
        sb.append(" g.unit AS unit,");
        sb.append(" g.num AS num,");
        sb.append(" g.quote_count AS quoteCount,");
        sb.append(" g.partake_count AS partakeCount");
        sb.append(" FROM sourcingcenter.i_sourcingcenter_t_inquiry_goods AS g ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry AS i ON i.id = g.inquiry_id ");
        sb.append(" WHERE i.deleted = FALSE ");
        sb.append( " AND g.del = FALSE ");
        sb.append(" AND g.inquiry_goods_status IN ( 3, 4, 5 ) ");
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and i.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and i.createdTime <= '" + good.getEndTime() + "' ");
        }
        if ( StringUtils.isNotBlank(good.getOrgName())){
            sb.append(" and g.organization_company_name = '" + good.getOrgName() + "'");
        }
        return jdbcTemplate.queryForList(sb.toString());
    }


    public static List<OrgInfoDto> queryOrganizationInfo(CustomerStatisticsDto good) {
        StringBuffer sb=  new StringBuffer();
        sb.append("select id,name,parent_id ");
        sb.append( " from systemmanagement.i_systemmanagement_t_organization where deleted= 0 ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrgInfoDto.class));
    }





    public static String queryCommonPurchaseStatistics(CustomerStatisticsDto good,StringBuffer stringBuffer) {
        StringBuffer sb=  new StringBuffer();
        sb.append("select GROUP_CONCAT(DISTINCT trade.order_goods_id) ");
        sb.append(" from financialcenter.i_financialcenter_t_settle_detail detail ");
        sb.append(" LEFT JOIN financialcenter.i_financialcenter_t_settle settle ON settle.id = detail.settle_id ");
        sb.append(" LEFT JOIN financialcenter.i_financialcenter_t_trade trade ON trade.id = detail.trade_id ");
        sb.append(" where detail.deleted = 0 ");
        sb.append( " AND settle.deleted = 0 ");
        sb.append( " AND trade.deleted = 0 ");
        sb.append(" AND settle.STATUS <> 'Returned' ");
        if (stringBuffer.length() > 0){
            sb.append(" AND trade.organization_name in ('" + stringBuffer.toString() + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and settle.payment_time >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and settle.payment_time <= '" + good.getEndTime() + "' ");
        }
        return jdbcTemplate.queryForObject(sb.toString(),String.class);
    }


    public static List<Map<String,Object>> queryCommonPurchase(CustomerStatisticsDto good,String order_good_ids){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tco.name AS companyName,\n" +
                "\tC.ID AS customerOrderId,\n" +
                "\tC.client_order_id AS clientOrderId,\n" +
                "\to.ID AS customerOrderGoodId,\n" +
                "\to.client_order_goods_id AS clientOrderGoodsId,\n" +
                "\to.NAME,\n" +
                "\to.brand,\n" +
                "\to.model,\n" +
                "\to.spec,\n" +
                "\to.price,\n" +
                "\to.quantity,\n" +
                "\to.total,\n" +
                "\tDATE_FORMAT(o.createdTime, '%Y-%m-%d %H:%i:%s') AS orderTime,\n" +
                "\tCONCAT(EXTRACT( YEAR FROM o.createdTime ),'') AS years,\n" +
                "\tCONCAT(EXTRACT( MONTH FROM o.createdTime),'') AS months \n" +
                "FROM\n" +
                "\tordercenter.i_ordercenter_t_order_customer_goods o\n" +
                "\tLEFT JOIN ordercenter.i_ordercenter_t_order_vendor v ON o.order_vendor_id = v.ID\n" +
                "\tLEFT JOIN ordercenter.i_ordercenter_t_order_customer C ON v.order_customer_id = C.ID\n" +
                "\tLEFT JOIN systemmanagement.i_systemmanagement_t_organization co ON C.organization_name = co.name \n" +
                "WHERE\n" +
                "\to.deleted = 0 \n" +
                "\tAND o.client_order_goods_id <> '' ");

          if(StringUtils.isNotBlank(good.getOrgIds())){
              sb.append(" and (co.id = '"+good.getOrgIds()+"' OR co.parent_id = '"+good.getOrgIds()+"')");
          }
        if (StringUtils.isNotBlank(order_good_ids)){
            sb.append(" and o.id in ('" + order_good_ids + "')");
        }

        return jdbcTemplate.queryForList(sb.toString());
    }



    public static List<CustomerStatisticResultDto>  queryBaojiaCount(StringBuffer sbNames, CustomerStatisticsDto good) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT temp.time AS time,");
        sb.append("sum( temp.count ) AS count,");
        sb.append("temp.id AS organizationId ,");
        sb.append("temp.name from (");
        sb.append(" SELECT");
        sb.append(" date_format(tqg.createdTime,'%Y-%m-%d')AS time,");
        sb.append(" count(*) AS count,");
        sb.append(" ti.organization_id AS id ,");
        sb.append(" ti.organization_company_name AS name ");
        sb.append(" from sourcingcenter.i_sourcingcenter_t_quotation_goods tqg");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods tig ON tig.id = tqg.inquiry_goods_id");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry ti ON ti.id = tig.inquiry_id ");
        sb.append(" WHERE tqg.quotation_goods_status IN ( 3, 4, 5, 6 ) AND tqg.deleted = 0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and ti.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and tqg.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and tqg.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by tqg.createdTime,ti.organization_id )temp ");
        sb.append(" group by temp.time,temp.id ORDER BY temp.time ASC; ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }
    public static List<CustomerStatisticResultDto> queryInquiryNum(StringBuffer sbNames, CustomerStatisticsDto good) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT temp.time AS time,");
        sb.append("sum( temp.count ) AS count,");
        sb.append("temp.id AS organizationId ,");
        sb.append("temp.name from (");
        sb.append(" SELECT");
        sb.append(" date_format(tig.createdTime,'%Y-%m-%d')AS time,");
        sb.append(" count(*) AS count,");
        sb.append(" ti.organization_id AS id ,");
        sb.append(" ti.organization_company_name AS name ");
        sb.append(" from sourcingcenter.i_sourcingcenter_t_inquiry_goods tig");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry ti ON ti.id = tig.inquiry_id and ti.deleted=0 ");
        sb.append(" WHERE tig.inquiry_goods_status IN ( 3, 4, 5) AND tig.deleted = 0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and ti.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and tig.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and tig.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by tig.createdTime,ti.organization_id )temp ");
        sb.append(" group by temp.time,temp.id ORDER BY temp.time ASC; ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }

    public static List<CustomerStatisticResultDto> queryDingbiaoCount(StringBuffer sbNames, CustomerStatisticsDto good) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT temp.time AS time,");
        sb.append("sum( temp.count ) AS count,");
        sb.append("temp.id AS organizationId ,");
        sb.append("temp.name from (");
        sb.append(" SELECT");
        sb.append(" date_format(tqg.modifiedTime,'%Y-%m-%d')AS time,");
        sb.append(" count(*) AS count,");
        sb.append(" ti.organization_id AS id ,");
        sb.append(" ti.organization_company_name AS name ");
        sb.append(" from sourcingcenter.i_sourcingcenter_t_quotation_goods tqg");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods tig ON tig.id = tqg.inquiry_goods_id");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry ti ON ti.id = tig.inquiry_id ");
        sb.append(" WHERE tqg.quotation_goods_status IN ( 5) AND tqg.deleted = 0 ");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and ti.organization_company_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and tqg.modifiedTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and tqg.modifiedTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by tqg.modifiedTime,ti.organization_id )temp ");
        sb.append(" group by temp.time,temp.id ORDER BY temp.time ASC; ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }
    public static List<CustomerStatisticResultDto> queryOrderNum(StringBuffer sbNames, CustomerStatisticsDto good) {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT temp.time AS time,");
        sb.append("sum( temp.count ) AS count,");
        sb.append("temp.id AS organizationId ,");
        sb.append("temp.name from (");
        sb.append(" SELECT");
        sb.append(" date_format(tco.createdTime,'%Y-%m-%d')AS time,");
        sb.append(" count(*) AS count,");
        sb.append(" tco.organization_id AS id ,");
        sb.append(" tco.organization_name AS name ");
        sb.append(" from ordercenter.i_ordercenter_t_order_customer tco");
        sb.append(" WHERE 1=1 AND tco.deleted = 0 ");
        sb.append( "and( ( tco.order_customer_status ='UpcomingPayment') or ( tco.order_customer_status in ('UpcomingConfirm','UpcomingDelivery','DoneDelivery') ) or (tco.order_customer_status ='Done') )");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and tco.organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and tco.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and tco.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by tco.createdTime,tco.organization_id )temp ");
        sb.append(" group by temp.time,temp.id ORDER BY temp.time ASC; ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));

    }

    public static List<CustomerStatisticResultDto> queryOrderGoodNum(StringBuffer sbNames, CustomerStatisticsDto good) {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT temp.time AS time,");
        sb.append("sum( temp.count ) AS count,");
        sb.append("temp.id AS organizationId ,");
        sb.append("temp.name from (");
        sb.append(" SELECT");
        sb.append(" date_format(tco.createdTime,'%Y-%m-%d')AS time,");
        sb.append(" count(*) AS count,");
        sb.append(" tvc.organization_id AS id ,");
        sb.append(" tvc.organization_name AS name ");
        sb.append(" from ordercenter.i_ordercenter_t_order_customer_goods tco");
        sb.append(" LEFT JOIN ordercenter.i_ordercenter_t_order_vendor tvo ON tvo.id = tco.order_vendor_id");
        sb.append(" LEFT JOIN ordercenter.i_ordercenter_t_order_customer tvc ON tvc.id = tvo.order_customer_id");
        sb.append(" WHERE 1=1 AND tco.deleted = 0 ");
        sb.append( "and( tvo.order_vendor_status in ('UpcomingPayment','UpcomingConfirm','UpcomingPayment','Done') )");
        if (sbNames != null && sbNames.length() > 0){
            sb.append(" and tvc.organization_name in ('" + sbNames + "')");
        }
        if (StringUtils.isNotBlank(good.getStartTime())){
            sb.append(" and tco.createdTime >= '" + good.getStartTime() + "' ");
        }
        if (StringUtils.isNotBlank(good.getEndTime())){
            sb.append(" and tco.createdTime <= '" + good.getEndTime() + "' ");
        }
        sb.append(" group by tco.createdTime,tvc.organization_id )temp ");
        sb.append(" group by temp.time,temp.id ORDER BY temp.time ASC; ");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CustomerStatisticResultDto.class));
    }

    public static Integer queryAllPlateUserCount(CustomerStatisticsDto good) {
        NamedParameterJdbcTemplate nameParameterJdbcTemplate = ApplicationContextUtils.getBean(NamedParameterJdbcTemplate.class);
        Map<String,Object> param = new HashMap<>();
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) from operation1center.i_operation1center_t_platform_grant_user  where 1=1 and  member_name IS NOT NULL and organization_name is not null and deleted =0 GROUP BY member_name ");
        return nameParameterJdbcTemplate.queryForObject(sb.toString(), param, Integer.class);
    }
    public static List<Map<String,Object>> queryAllPlateUserList(CustomerStatisticsDto good,Integer start,Integer size) {
        Map<String,Object> param = new HashMap<>();
        StringBuffer sb = new StringBuffer();
        sb.append("select distinct JSON_UNQUOTE(json_extract(organization_id,'$.id')) id");
        sb.append(" member_name userName,JSON_UNQUOTE(json_extract( member_id, '$.id' )) user_id,GROUP_CONCAT(organization_name) name ");
        sb.append(" from operation1center.i_operation1center_t_platform_grant_user ");
        sb.append("  where 1=1 and  member_name IS NOT NULL and organization_name is not null and deleted =0 GROUP BY member_id");
        if (start != null  && size != null){
            sb.append( " limit "+start +"," + size);
        }
        return jdbcTemplate.queryForList(sb.toString());
    }

    public static List<Map<String, Object>> queryAllPlateOrganization() {
        StringBuffer sb = new StringBuffer();
        sb.append(" select distinct JSON_UNQUOTE(json_extract( organization_id, '$.id' )) id,");
        sb.append(" organization_name  name ");
        sb.append(" from operation1center.i_operation1center_t_platform_grant_user ");
        sb.append(" where member_name IS NOT NULL  AND organization_name IS NOT NULL AND deleted = 0 ");
        return jdbcTemplate.queryForList(sb.toString());

    }
}
